package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.AllotAreaSet;
import com.xmy.cultivate.entity.AllotTeacherWheel;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.Staff;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 * 校区分配教师的轮数 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2025-06-07
 */
public interface AllotTeacherWheelMapper extends BaseMapper<AllotTeacherWheel> {


    /**
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param stationId
     * @param subjectsId
     * @param areaId
     * @param stationType 1获取数据，2获取校区id
     * @return
     */
    @Select({"<script>",
                "WITH with_tb01 AS (",
                          "SELECT",
                            "t1.school_id,",
                            "t1.station_id,",
                            "t1.subjects_id,",
                            "t1.department_id,",
                            "t1.need_count,",
                            "t3.`name` AS department_name,",
                            "CONCAT(",
                              "t2.NAME,",
                            "IFNULL( t4.NAME, '' )) AS station_needs_name,",
                            "( CASE WHEN t2.type = 1 THEN t1.school_id ELSE t1.department_id END ) AS school_or_department_id,",
                            "( CASE WHEN t2.type = 1 THEN t5.NAME ELSE t3.`name` END ) AS school_or_department_name ",
                          "FROM",
                            "adms_school_needs AS t1",
                            "INNER JOIN adms_station AS t2 ON t1.station_id = t2.id",
                            "LEFT JOIN department AS t3 ON t1.department_id = t3.id",
                            "LEFT JOIN subjects AS t4 ON t1.subjects_id = t4.id",
                            "LEFT JOIN organization AS t5 ON t1.school_id = t5.id ",
                          "WHERE",
                            "1 = 1 ",
                            "AND t1.status = 1 ",
                            "AND t1.station_id = ${stationId} ",
                            "AND t1.subjects_id = ${subjectsId} ",
                            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                                "and t1.school_id = ${schoolId}",
                            "</when>",
                          "),",
                          "with_tb02 AS (",
                          "SELECT",
                            "t1.try_date,",
                            "t1.pass_type,",
                            "t1.try_audit_date,",
                            "t2.`name`,",
                            "t2.phone,",
                            "t2.inviter_id,",
                            "t3.station_id,",
                            "t3.subjects_id,",
                            "t3.department_id,",
                            "t1.school_id,",
                            "( CASE WHEN t3.type = 1 THEN t1.school_id ELSE t3.department_id END ) AS school_or_department_id,",
                            "( CASE WHEN t3.type = 1 THEN t4.NAME ELSE t5.`name` END ) AS school_or_department_name,",
                          "CASE",
                              "",
                              "WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t2.inviter_id AND role_id = 1568160915052400644 ) THEN",
                              "1 ELSE 2 ",
                            "END AS role_id ",
                          "FROM",
                            "adms_try_job AS t1",
                            "INNER JOIN adms_recruit AS t2 ON t2.id = t1.recruit_id",
                            "INNER JOIN adms_station_needs AS t3 ON t1.station_needs_id = t3.id",
                            "INNER JOIN organization AS t4 ON t1.school_id = t4.id",
                            "LEFT JOIN department AS t5 ON t3.department_id = t5.id ",
                          "WHERE",
                            "1 = 1 ",
                            "AND is_arrive_job = 0 ",
                          ") ",
                          "",
                        "SELECT",
                        "<if test= 'stationType == 1'>",
                          "count(*) as count",
                        "</if>",
                        "<if test= 'stationType == 2'>",
                            "school_id",
                        "</if>",
                        "FROM",
                          "(",
                          "SELECT",
                            "tt.school_id,",
                            "tt.needCount,",
                            "tt.tryJobRecruitCount,",
                            "tt.station_id,",
                            "tt.subjects_id,",
                            "tt.school_or_department_id,",
                            "tt.school_or_department_name,",
                            "( tt.needCount - tt.tryJobRecruitCount ) AS needRecruitment ",
                          "FROM",
                            "(",
                            "SELECT",
                              "sum( need_count ) AS needCount,",
                              "SUM( tryJobRecruitCount ) AS tryJobRecruitCount,",
                              "school_id,",
                              "station_id,",
                              "subjects_id,",
                              "school_or_department_id,",
                              "school_or_department_name ",
                            "FROM",
                              "(",
                              "SELECT",
                                "need_count,",
                                "SUM( CASE WHEN pass_type IN ( 0, 1 ) THEN 1 ELSE 0 END ) AS tryJobRecruitCount,",
                                "with_tb01.school_id,",
                                "with_tb01.school_or_department_id,",
                                "with_tb01.school_or_department_name,",
                                "with_tb01.station_id,",
                                "with_tb01.subjects_id ",
                              "FROM",
                                "with_tb01",
                                "LEFT JOIN with_tb02 AS t01 ON t01.school_or_department_id = with_tb01.school_or_department_id ",
                                "AND t01.subjects_id = with_tb01.subjects_id ",
                                "AND t01.station_id = with_tb01.station_id ",
                              "GROUP BY",
                                "with_tb01.school_or_department_id,",
                                "with_tb01.station_id,",
                                "with_tb01.subjects_id ",
                              ") AS t ",
                            "GROUP BY",
                              "school_or_department_id ",
                            ") AS tt",
                            "<if test= 'stationType == 1'>",
                                "INNER JOIN adms_allot_area_set AS tt2 ON tt.school_id = tt2.school_id ",
                                "AND tt2.area_id = ${areaId} ",
                            "</if>",
                          "HAVING",
                          "( needRecruitment &gt; 0 ) ",
                          ") AS rs_tb",
                        "<if test= 'stationType == 2'>",
                            "limit 1",
                        "</if>",
            "</script>"})
    public Long getSchoolNeedsCount(@Param("yearPart") Integer yearPart,
                            @Param("quarterNum") Integer quarterNum,
                            @Param("schoolId") Long schoolId,
                            @Param("stationId") Long stationId,
                            @Param("subjectsId") Long subjectsId,
                            @Param("areaId") Long areaId,
                            @Param("stationType") Integer stationType);

    /**
     * 判断是否有校区有此岗位需求
     * @param yearPart
     * @param quarterNum
     * @param stationNeedsId
     * @return
     */
    @Select({"<script>",
            "WITH school_needs_with AS ( SELECT school_id,station_id as station_needs_id, SUM( need_count ) AS need_count FROM adms_school_needs WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} and station_id = 0 GROUP BY school_id, station_id ),",
            "try_job_with AS (",
            "SELECT t1.school_id,t2.station_needs_id,",
            "SUM( CASE WHEN ( t1.is_arrive_job = 0 AND t1.pass_type = 0 ) OR ( t1.pass_type = 1 ) THEN 1 ELSE 0  END  ) AS yet_count ",
            "FROM adms_try_job AS t1 INNER JOIN adms_recruit AS t2 ON t1.recruit_id = t2.id WHERE t1.year_part = ${yearPart} AND t1.quarter_num = ${quarterNum} and t2.station_needs_id = ${stationNeedsId} GROUP BY t1.school_id, t2.station_needs_id ),",
            "school_needs_deff_with AS (",
            "SELECT",
            "t01.school_id,t01.need_count,t01.station_needs_id,IFNULL(t02.yet_count,0) as yet_count ",
            "FROM",
            "school_needs_with AS t01 LEFT JOIN try_job_with AS t02 ON t01.school_id = t02.school_id and t01.station_needs_id = t02.station_needs_id HAVING(need_count &gt; yet_count)",
            "),",
            "school_whith as (select id as school_id,IF(pid &gt; 0,1,0) as pid from organization),",
            "allot_teacher_with as(select alt.*,als.sort from adms_allot_teacher_wheel as alt INNER JOIN adms_allot_teacher_sort as als on alt.school_id = als.school_id where alt.round_num = ${roundNum})",
            "SELECT count(*) as count  FROM school_needs_deff_with as sch_n",
            "<if test= 'areaId != 0'>",
                "INNER JOIN area_school as asch on sch_n.school_id = asch.school_id and asch.area_id = ${areaId} ",
            "</if>",
            "</script>"})
    public Integer getSchoolNeedsCountV2(@Param("yearPart") Integer yearPart,
                                            @Param("quarterNum") Integer quarterNum,
                                            @Param("stationNeedsId") Long stationNeedsId,
                                            @Param("areaId") Long areaId
    );

    /**
     *获取大小校区需求
     * @param yearPart
     * @param quarterNum
     * @param stationNeedsId
     * @param pidStr 0小校区，1大校区，0,1大小校区
     * @return
     */
    @Select({"<script>",
            "WITH school_needs_with AS ( SELECT school_id,station_needs_id, SUM( need_count ) AS need_count FROM adms_school_needs WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} and station_needs_id = ${stationNeedsId} GROUP BY school_id, station_needs_id ),",
            "try_job_with AS (",
            "SELECT t1.school_id,t2.station_needs_id,",
            "SUM( CASE WHEN ( t1.is_arrive_job = 0 AND t1.pass_type = 0 ) OR ( t1.pass_type = 1 ) THEN 1 ELSE 0  END  ) AS yet_count ",
            "FROM adms_try_job AS t1 INNER JOIN adms_recruit AS t2 ON t1.recruit_id = t2.id WHERE t1.year_part = ${yearPart} AND t1.quarter_num = ${quarterNum} and t2.station_needs_id = ${stationNeedsId} GROUP BY t1.school_id, t2.station_needs_id ),",
            "school_needs_deff_with AS (",
            "SELECT",
            "t01.school_id,t01.need_count,t01.station_needs_id,IFNULL(t02.yet_count,0) as yet_count ",
            "FROM",
            "school_needs_with AS t01 LEFT JOIN try_job_with AS t02 ON t01.school_id = t02.school_id and t01.station_needs_id = t02.station_needs_id HAVING(need_count &gt; yet_count)",
            "),",
            "school_whith as (select id as school_id,IF(pid &gt; 0,1,0) as pid from organization)",
            //",allot_teacher_with as(select alt.*,als.sort from adms_allot_teacher_wheel as alt INNER JOIN adms_allot_teacher_sort as als on alt.school_id = als.school_id where alt.lowest_num &gt; yet_num and alt.round_num = ${roundNum} alt.station_needs_id = ${stationNeedsId})",
            "SELECT sn.*,sw.pid  FROM school_needs_deff_with as sn INNER JOIN school_whith as sw on sn.school_id = sw.school_id ",
            "<if test= 'areaId != 0'>",
                "INNER JOIN area_school as asch on sn.school_id = asch.school_id and asch.area_id = ${areaId} ",
                " where sw.pid in(${pidStr})",
            "</if>",
            "</script>"})
    public List<AllotTeacherWheel> getSchoolNeedsList(@Param("yearPart") Integer yearPart,
                                       @Param("quarterNum") Integer quarterNum,
                                       @Param("stationNeedsId") Long stationNeedsId,
                                       @Param("areaId") Long areaId,
                                       @Param("pidStr") String pidStr
    );



    @Select({"<script>",
                "WITH with_tb01 AS (",
                          "SELECT",
                            "t1.school_id,",
                            "t1.station_id,",
                            "t1.subjects_id,",
                            "t1.department_id,",
                            "t1.need_count,",
                            "t3.`name` AS department_name,",
                            "CONCAT(",
                              "t2.NAME,",
                            "IFNULL( t4.NAME, '' )) AS station_needs_name,",
                            "( CASE WHEN t2.type = 1 THEN t1.school_id ELSE t1.department_id END ) AS school_or_department_id,",
                            "( CASE WHEN t2.type = 1 THEN t5.NAME ELSE t3.`name` END ) AS school_or_department_name ",
                          "FROM",
                            "adms_school_needs AS t1",
                            "INNER JOIN adms_station AS t2 ON t1.station_id = t2.id",
                            "LEFT JOIN department AS t3 ON t1.department_id = t3.id",
                            "LEFT JOIN subjects AS t4 ON t1.subjects_id = t4.id",
                            "LEFT JOIN organization AS t5 ON t1.school_id = t5.id ",
                          "WHERE",
                            "1 = 1 ",
                            "AND t1.status = 1 ",
                            "AND t1.station_id = ${stationId} ",
                            "AND t1.subjects_id = ${subjectsId} ",
                          "),",
                          "with_tb02 AS (",
                          "SELECT",
                            "t1.try_date,",
                            "t1.pass_type,",
                            "t1.try_audit_date,",
                            "t2.`name`,",
                            "t2.phone,",
                            "t2.inviter_id,",
                            "t3.station_id,",
                            "t3.subjects_id,",
                            "t3.department_id,",
                            "t1.school_id,",
                            "( CASE WHEN t3.type = 1 THEN t1.school_id ELSE t3.department_id END ) AS school_or_department_id,",
                            "( CASE WHEN t3.type = 1 THEN t4.NAME ELSE t5.`name` END ) AS school_or_department_name,",
                          "CASE",
                              "",
                              "WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t2.inviter_id AND role_id = 1568160915052400644 ) THEN",
                              "1 ELSE 2 ",
                            "END AS role_id ",
                          "FROM",
                            "adms_try_job AS t1",
                            "INNER JOIN adms_recruit AS t2 ON t2.id = t1.recruit_id",
                            "INNER JOIN adms_station_needs AS t3 ON t1.station_needs_id = t3.id",
                            "INNER JOIN organization AS t4 ON t1.school_id = t4.id",
                            "LEFT JOIN department AS t5 ON t3.department_id = t5.id ",
                          "WHERE",
                            "1 = 1 ",
                            "AND is_arrive_job = 0 ",
                          "),",
                          "with_tb03 AS (",
                          "SELECT",
                            "adms_allot_teacher_wheel.school_id,",
                            "adms_allot_teacher_wheel.station_id,",
                            "adms_allot_teacher_wheel.subjects_id,",
                            "adms_allot_teacher_wheel.lowest_num,",
                            "adms_allot_teacher_wheel.yet_num,",
                            "adms_allot_area_set.area_id,",
                            "adms_allot_area_set.sort ",
                          "FROM",
                            "adms_allot_teacher_wheel",
                            "INNER JOIN adms_allot_area_set ON adms_allot_teacher_wheel.school_id = adms_allot_area_set.school_id ",
                          ")",
                        "SELECT",
                          "tt.school_id,",
                          "tt.needCount,",
                          "tt.tryJobRecruitCount,",
                          "tt.station_id,",
                          "tt.subjects_id,",
                          "tt.school_or_department_id,",
                          "tt.school_or_department_name,",
                          "( tt.needCount - tt.tryJobRecruitCount ) AS needRecruitment ",
                        "FROM",
                          "(",
                          "SELECT",
                            "sum( need_count ) AS needCount,",
                            "SUM( tryJobRecruitCount ) AS tryJobRecruitCount,",
                            "school_id,",
                            "station_id,",
                            "subjects_id,",
                            "school_or_department_id,",
                            "school_or_department_name ",
                          "FROM",
                            "(",
                            "SELECT",
                              "need_count,",
                              "SUM( CASE WHEN pass_type IN ( 0, 1 ) THEN 1 ELSE 0 END ) AS tryJobRecruitCount,",
                              "with_tb01.school_id,",
                              "with_tb01.school_or_department_id,",
                              "with_tb01.school_or_department_name,",
                              "with_tb01.station_id,",
                              "with_tb01.subjects_id ",
                            "FROM",
                              "with_tb01",
                              "LEFT JOIN with_tb02 AS t01 ON t01.school_or_department_id = with_tb01.school_or_department_id ",
                              "AND t01.subjects_id = with_tb01.subjects_id ",
                              "AND t01.station_id = with_tb01.station_id ",
                            "GROUP BY",
                              "with_tb01.school_or_department_id,",
                              "with_tb01.station_id,",
                              "with_tb01.subjects_id ",
                            ") AS t ",
                          "GROUP BY",
                            "school_or_department_id ",
                          ") AS tt",
                          "INNER JOIN with_tb03 AS with_tb03 ON tt.school_id = with_tb03.school_id ",
                          "AND tt.station_id = with_tb03.station_id ",
                          "AND tt.subjects_id = with_tb03.subjects_id ",
                          "AND with_tb03.area_id = ${areaId}",
                        "WHERE",
                          "with_tb03.lowest_num &gt; yet_num ",
                        "HAVING",
                          "( needRecruitment &gt; 0 ) ",
                        "ORDER BY",
                          "yet_num,with_tb03.sort ASC limit 1",
            "</script>"})
    public AllotTeacherWheel getOneForNeeds(@Param("yearPart") Integer yearPart,
                                            @Param("quarterNum") Integer quarterNum,
                                            @Param("stationId") Long stationId,
                                            @Param("subjectsId") Long subjectsId,
                                            @Param("areaId") Long areaId);


    @Select({"<script>",
                    "WITH school_needs_with AS ( SELECT school_id,station_needs_id, SUM( need_count ) AS need_count FROM adms_school_needs WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} and station_needs_id = ${stationNeedsId} GROUP BY school_id, station_needs_id ),",
                    "try_job_with AS (",
                      "SELECT t1.school_id,t2.station_needs_id,",
                        "SUM( CASE WHEN ( t1.is_arrive_job = 0 AND t1.pass_type = 0 ) OR ( t1.pass_type = 1 ) THEN 1 ELSE 0  END  ) AS yet_count ",
                          "FROM adms_try_job AS t1 INNER JOIN adms_recruit AS t2 ON t1.recruit_id = t2.id WHERE t1.year_part = ${yearPart} AND t1.quarter_num = ${quarterNum} and t2.station_needs_id = ${stationNeedsId} GROUP BY t1.school_id, t2.station_needs_id ),",
                    "school_needs_deff_with AS (",
                          "SELECT",
                            "t01.school_id,t01.need_count,t01.station_needs_id,IFNULL(t02.yet_count,0) as yet_count ",
                          "FROM",
                            "school_needs_with AS t01 LEFT JOIN try_job_with AS t02 ON t01.school_id = t02.school_id and t01.station_needs_id = t02.station_needs_id HAVING(need_count &gt; yet_count)",
                          "),",
                          "school_whith as (select id as school_id,IF(pid &gt; 0,1,0) as pid from organization),",
                          "allot_teacher_with as(select alt.*,als.sort from adms_allot_teacher_wheel as alt INNER JOIN adms_allot_teacher_sort as als on alt.school_id = als.school_id where alt.lowest_num &gt; yet_num and alt.round_num = ${roundNum} and alt.station_needs_id = ${stationNeedsId})",
                    "SELECT sn.*  FROM school_needs_deff_with as sn INNER JOIN allot_teacher_with as sch on sn.school_id = sch.school_id and sn.station_needs_id = sch.station_needs_id ",
                    "<if test= 'areaId != 0'>",
                        "INNER JOIN area_school as asch on sn.school_id = asch.school_id and asch.area_id = ${areaId}",
                    "</if>",
                    " ORDER BY sch.yet_num,sch.sort asc limit 1",
    "</script>"})
    public AllotTeacherWheel getOneForNeedsV2(@Param("yearPart") Integer yearPart,
                                            @Param("quarterNum") Integer quarterNum,
                                            @Param("stationNeedsId") Long stationNeedsId,
                                            @Param("areaId") Long areaId,
                                            @Param("roundNum") Integer roundNum
                                            );

    /**
     * 获取是否存在轮数,版本升级，不分年份，学期
     * @param yearPart
     * @param quarterNum
     * @param stationId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select IFNULL(MAX(round_num),0) as round_num from adms_allot_teacher_wheel where station_id = ${stationId} and subjects_id = ${subjectsId} and area_id = ${areaId}",
    "</script>"})
    public Integer getWheelMaxRoundNum(@Param("yearPart") Integer yearPart,
                                 @Param("quarterNum") Integer quarterNum,
                                 @Param("stationId") Long stationId,
                                 @Param("subjectsId") Long subjectsId,
                                 @Param("areaId") Long areaId
                                       );

    /**
     * 获取还未添加的校区
     * @param yearPart
     * @param quarterNum
     * @param stationId
     * @param subjectsId
     * @param areaId
     * @return
     */
    @Select("select t1.* from adms_allot_area_set as t1 LEFT JOIN adms_allot_teacher_wheel as t2 on t1.school_id = t2.school_id and t1.area_id = t2.area_id and  station_id = ${stationId} and subjects_id = ${subjectsId} where t1.area_id = ${areaId}  and t2.id is null")
    public List<AllotAreaSet> getUnAddList(@Param("yearPart") Integer yearPart,
                                           @Param("quarterNum") Integer quarterNum,
                                           @Param("stationId") Long stationId,
                                           @Param("subjectsId") Long subjectsId,
                                           @Param("areaId") Long areaId);

    @Update("update adms_allot_teacher_wheel set yet_num = 0 where area_id = ${areaId} and station_id = ${stationId} and subjects_id = ${subjectsId} ")//lowest_num >= yet_num and
    public boolean updateYetNum(@Param("stationId") Long stationId,
                                @Param("subjectsId") Long subjectsId,
                                @Param("areaId") Long areaId);


    /**
     * 获取员工所属大校区
     * @param yearPart
     * @param quarterNum
     * @param teacherId
     * @return
     */
    @Select({"<script>",
            "WITH with_tb01 AS ( SELECT school_id, IF ( pid = 0, school_id, pid ) AS pid FROM school_quarter WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} )",
                    "select t1.id,with_tb01.pid as school_id from staff as t1 INNER JOIN with_tb01 on t1.school_id = with_tb01.school_id where t1.id = ${teacherId}",
            "</script>"})
    public Staff getStaffSchoolInfo(@Param("yearPart") Integer yearPart,
                                    @Param("quarterNum") Integer quarterNum,
                                    @Param("teacherId") Long teacherId
                                    );


}
